package org.mark.sell.mapper;

import org.mark.sell.entity.DeliveryProcess;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface DeliveryProcessMapper extends BaseMapper<DeliveryProcess> {

    @Select("SELECT " +
            "di.user_name, " +
            "COUNT(dp.id) as total_orders, " +
            "SUM(dp.number) as total_number, " +
            "SUM(dp.price * dp.number) as total_amount, " +
            "SUM(CASE WHEN dp.is_promotion = '1' THEN (dp.price * dp.number) ELSE 0 END) as promotion_amount " +
            "FROM t_delivery_process dp " +
            "LEFT JOIN t_delivery_info di ON dp.delivery_id = di.id " +
            "WHERE dp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND (#{userName} IS NULL OR di.user_name LIKE CONCAT('%', #{userName}, '%')) " +
            "AND (#{freshFoodName} IS NULL OR dp.fresh_food_name LIKE CONCAT('%', #{freshFoodName}, '%')) " +
            "GROUP BY di.user_name")
    List<Map<String, Object>> getDeliveryStats(String startTime, String endTime, String userName, String freshFoodName);

    @Select("SELECT " +
            "dp.fresh_food_name, " +
            "COUNT(dp.id) as total_orders, " +
            "SUM(dp.number) as total_number, " +
            "SUM(dp.price * dp.number) as total_amount, " +
            "SUM(CASE WHEN dp.is_promotion = '1' THEN (dp.price * dp.number) ELSE 0 END) as promotion_amount " +
            "FROM t_delivery_process dp " +
            "WHERE dp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND (#{userName} IS NULL OR EXISTS (" +
            "    SELECT 1 FROM t_delivery_info di " +
            "    WHERE di.id = dp.delivery_id " +
            "    AND di.user_name LIKE CONCAT('%', #{userName}, '%')" +
            ")) " +
            "AND (#{freshFoodName} IS NULL OR dp.fresh_food_name LIKE CONCAT('%', #{freshFoodName}, '%')) " +
            "GROUP BY dp.fresh_food_name")
    List<Map<String, Object>> getFreshStats(String startTime, String endTime, String userName, String freshFoodName);
}